﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using PlatForm.DataLayer;

namespace Helper
{
    class Program
    {
        public static void ImportStore()
        {
            var tempFile = new FileInfo("C:\\Users\\NHAN\\Google Drive\\TheFruitRepublic\\Thong tin.xlsx");
         
            using (var package = new ExcelPackage(tempFile))
            {
                var lstST = new List<Store>();
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();

                        for (int rowNumber = 3;
                             rowNumber <= 22;
                             rowNumber++)
                        // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                        {
                            lstST.Add(new Store()
                            {
                                ST_No = currentWorksheet.Cells[rowNumber, 6].Value.ToString(),
                                ST_Name = currentWorksheet.Cells[rowNumber, 7].Value.ToString()
                            });

                        }
                        Store.Objects.Insert(lstST);
                    }
                }
            }
        }

        public static void ImportSUPP()
        {
            var tempFile = new FileInfo("C:\\Users\\NHAN\\Google Drive\\TheFruitRepublic\\Thong tin.xlsx");

            using (var package = new ExcelPackage(tempFile))
            {
                var lstST = new List<Supplier>();
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();

                        for (int rowNumber = 4;
                             rowNumber <= 87;
                             rowNumber++)
                        // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                        {
                            if (currentWorksheet.Cells[rowNumber, 3].Value != null)
                            {
                                lstST.Add(new Supplier()
                                    {
                                        SUPPL_No = currentWorksheet.Cells[rowNumber, 3].Value.ToString().Trim(),
                                        SUPPL_Name = currentWorksheet.Cells[rowNumber, 2].Value.ToString().Trim(),
                                        Address =
                                            currentWorksheet.Cells[rowNumber, 4].Value == null
                                                ? ""
                                                : currentWorksheet.Cells[rowNumber, 4].Value.ToString().Trim()
                                    });
                            }
                        }
                        Supplier.Objects.Insert(lstST);
                    }
                }
            }
        }

        public static void ImportART()
        {
            var tempFile = new FileInfo("C:\\Users\\NHAN\\Google Drive\\TheFruitRepublic\\Thong tin.xlsx");

            using (var package = new ExcelPackage(tempFile))
            {
                var lstST = new List<Article>();
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();

                        for (int rowNumber = 4;
                             rowNumber <= 338;
                             rowNumber++)
                        // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                        {
                            if (currentWorksheet.Cells[rowNumber, 10].Value != null && lstST.FirstOrDefault(x=>x.ART_No== currentWorksheet.Cells[rowNumber, 10].Value.ToString())==null)
                            {
                                lstST.Add(new Article()
                                {
                                    ART_No = currentWorksheet.Cells[rowNumber, 10].Value.ToString().Trim(),
                                    ART_Name = currentWorksheet.Cells[rowNumber, 11].Value.ToString().Trim(),
                                    Cat = int.Parse(currentWorksheet.Cells[rowNumber, 14].Value.ToString().Trim()[currentWorksheet.Cells[rowNumber, 14].Value.ToString().Trim().Length-1].ToString()),
                                    Unit = float.Parse(currentWorksheet.Cells[rowNumber, 33].Value.ToString().Trim()),
                                    LastUpdated = DateTime.Now
                                });
                            }
                        }
                        Article.Objects.Insert(lstST);
                    }
                }
            }
        }

        private static void Main(string[] args)
        {
            ImportART();
        }
    }
}
